from datetime import datetime
import os
import xlrd
import xlwt
import json
import logging



from django.contrib.auth.decorators import login_required
from django.contrib.auth.mixins import LoginRequiredMixin
from django.utils.decorators import method_decorator
from utils.visit_info import change_info

from utils.len_col import len_col,write_excel
from urllib.parse import urlencode
from io import StringIO, BytesIO
from xlwt import *
from django.db import transaction
from django.http import JsonResponse, HttpResponse
from django.shortcuts import render
from django.views import View
from apps.searches.models import *
from apps.users.models import *
from utils.json_fun import to_json_data, my_decorator
from utils.res_code import Code, error_map
from django.core.paginator import Paginator, EmptyPage
from utils import paginator_script
from . import constants
from django.utils.encoding import escape_uri_path


# 导入日志器
logger = logging.getLogger('django')


class Love(View):
    """
    test index
    """
    # @method_decorator(login_required(login_url="users:login"))
    # @method_decorator(my_decorator)
    def get(self,request):

        return render(request, 'searches/Love.html')

class Birthday(View):
    """
    test index
    """
    # @method_decorator(login_required(login_url="users:login"))
    # @method_decorator(my_decorator)
    def get(self,request):

        return render(request, 'searches/birthday.html')

class Loves(View):
    """
    test index
    """
    # @method_decorator(login_required(login_url="users:login"))
    # @method_decorator(my_decorator)
    def get(self,request):

        return render(request, 'searches/loves.html')


class Index(LoginRequiredMixin,View):
    """
    test index
    """
    # @method_decorator(login_required(login_url="users:login"))
    # @method_decorator(my_decorator)
    def get(self,request):
        change_info(request)
        ser_content=DesCond.objects.only("fname").filter(is_delete=False)
        context = {
            'ser_content': ser_content,
        }
        return render(request, 'searches/index.html',context=context)

    def post(self, request):
        change_info(request)
        # 1、从前端获取参数
        json_data = request.body
        if not json_data:
            return to_json_data(errno=Code.PARAMERR, errmsg=error_map[Code.PARAMERR])
        # 将json转化为dict
        dict_data = json.loads(json_data.decode('utf8'))
        user = getattr(request, 'user', None)
        username = user.first_name
        if not username:
            return to_json_data(errno=Code.DBERR, errmsg="上传失败,请先到后台添加正确姓名")
        username1 =user.username
        user1 = Users.objects.only("idcn_id").filter(username=username1).first()
        idcn_id = user1.idcn_id
        gdtime = dict_data["sGdtime"]
        gdjj = dict_data["sGdjj"]
        gdLink = dict_data["sGdLink"].strip()
        gdtype = dict_data["sGdType"].split('|')[0]
        gdnumber = int(dict_data["sGdNumber"])
        dc_content = DesCond.objects.only('btime','etime').filter(is_delete=False,fname=gdtype).first()
        dc_id = dc_content.id
        btime = dc_content.btime
        etime = int(dc_content.etime)
        hcount = btime+etime*gdnumber
        if gdtype == "资产盘点":
            hcount = hcount*0.24
        p_exist = Gd.objects.defer('update_time', 'create_time').filter(is_delete=False, cuser=username, dc_id=dc_id,
                                                                        gname=gdLink).first()
        if p_exist:
            p_exist.zb = gdtime
            p_exist.jj = gdjj
            p_exist.gcount = gdnumber
            p_exist.hcount = hcount
            p_exist.idcn_id = idcn_id
            p_exist.save()
        else:
            Gd.objects.get_or_create(gname=gdLink,gcount=gdnumber,hcount=hcount,cuser=username,dc_id=dc_id,idcn_id=idcn_id,zb=gdtime,jj=gdjj)
        return to_json_data(errmsg='文章创建成功')

def page_not_found(request,**kwargs):
    return render(request,"searches/404.html",status=404)


class DownloadExecl(LoginRequiredMixin,View):
    # download execl from mysql
    def post(self,request):
        change_info(request)
        """
           导出excel表格
        """
        jsondata = request.POST
        if not jsondata:
            return to_json_data(errno=Code.UNKOWNERR, errmsg="出入数据不能为空")
        data = json.loads(jsondata['json'])
        c_list = data["content"].split('\n')
        No1 = 0
        down_list=[]
        for i in c_list:
            Q_obj = IdcP.objects.select_related('idcr','idcn','pup','tf','oip','off','supplier')\
                .only('idcr__rname','idcn__name','pup__pname','tf__tname','off__fname','oip__oname','supplier__sname')\
                .filter(is_delete=False,pn=i)
            down_list.append(Q_obj)
            if Q_obj:
                No1+=1
        if No1:
            # 创建工作薄
            ws = Workbook(encoding='utf-8')
            w = ws.add_sheet(u"sheet1")
            w.write(0, 0, "机房名称")
            w.write(0, 1, "房间编号")
            w.write(0, 2, "资产编号")
            w.write(0, 3, "sn")
            w.write(0, 4, "机列号")
            w.write(0, 5, "机柜号")
            w.write(0, 6, "u位")
            w.write(0, 7, "内网ip")
            w.write(0, 8, "内网掩码")
            w.write(0, 9, "内网网关")
            w.write(0, 10, "外网ip")
            w.write(0, 11, "外网掩码")
            w.write(0, 12, "外网网关")
            w.write(0, 13, "带外ip")
            w.write(0, 14, "带外掩码")
            w.write(0, 15, "带外网关")
            w.write(0, 16, "内网trunk vlan")
            w.write(0, 17, "内网all vlan")
            w.write(0, 18, "安装模板")
            w.write(0, 19, "环境")
            w.write(0, 20, "设备型号")
            w.write(0, 21, "到货时间")
            w.write(0, 22, "是否交付")
            w.write(0, 23, "资产是否已更新")
            w.write(0, 24, "带外是否开通")
            w.write(0, 25, "搬迁")
            w.write(0, 26, "下线")
            w.write(0, 27, "硬件变更")
            w.write(0, 28, "重装")
            w.write(0, 29, "备注")
            w.write(0, 30, "供应商")
            # 写入数据
            excel_row = 1
            for _ in down_list:
                for obj in _:
                    w.write(excel_row, 0, obj.idcn.name)
                    w.write(excel_row, 1, obj.idcr.rname)
                    w.write(excel_row, 2, obj.pn)
                    w.write(excel_row, 3, obj.sn)
                    w.write(excel_row, 4, obj.mt)
                    w.write(excel_row, 5, obj.cb)
                    w.write(excel_row, 6, obj.ul)
                    w.write(excel_row, 7, obj.nip)
                    w.write(excel_row, 8, obj.nmask)
                    w.write(excel_row, 9, obj.ngateway)
                    w.write(excel_row, 10, obj.wip)
                    w.write(excel_row, 11, obj.wmask)
                    w.write(excel_row, 12, obj.wgateway)
                    w.write(excel_row, 13, obj.mip)
                    w.write(excel_row, 14, obj.mmask)
                    w.write(excel_row, 15, obj.mgateway)
                    w.write(excel_row, 16, obj.ntvlan)
                    w.write(excel_row, 17, obj.navlan)
                    w.write(excel_row, 18, obj.imode)
                    w.write(excel_row, 19, obj.env)
                    w.write(excel_row, 20, obj.equ_ty)
                    w.write(excel_row, 21, obj.arrtime)
                    w.write(excel_row, 22, obj.tf.tname)
                    w.write(excel_row, 23, obj.pup.pname)
                    w.write(excel_row, 24, obj.oip.oname)
                    w.write(excel_row, 25, obj.moves)
                    w.write(excel_row, 26, obj.off.fname)
                    w.write(excel_row, 27, obj.Hwc)
                    w.write(excel_row, 28, obj.re_install)
                    w.write(excel_row, 29, obj.remarks)
                    w.write(excel_row, 30, obj.supplier.sname)
                    excel_row += 1
            # 检测文件是够存在
            # 方框中代码是保存本地文件使用，如不需要请删除该代码
            ###########################
            # exist_file = os.path.exists("test.xls")
            # if exist_file:
            #     os.remove(r"test.xls")
            # ws.save("test.xls")
            ############################
            sio = BytesIO()  # 这里原博客是StringIO.StringIO()，发现有点问题
            ws.save(sio)
            sio.seek(0)
            # final_filename = '建德内部资产.xls'
            # doc_filename = escape_uri_path(final_filename)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=jd.xls'
            response.write(sio.getvalue())
            return response
        else:
            return to_json_data(errno=Code.DBERR, errmsg="未查到相应数据")

class DownloadExeclone(LoginRequiredMixin,View):
    # download execl from mysql

    def post(self,request):
        change_info(request)
        """
           导出excel表格
        """
        # 设置字体
        font = xlwt.Font()
        font.bold = True

        # 设置边框
        borders = xlwt.Borders()
        borders.left = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN

        # 设置居中
        alignment = xlwt.Alignment()
        alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平方向
        alignment.vert = xlwt.Alignment.VERT_TOP  # 垂直方向

        # 设置背景颜色
        pattern = xlwt.Pattern()
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour = 3  # 背景颜色

        pattern1 = xlwt.Pattern()
        pattern1.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern1.pattern_fore_colour = 4  # 背景颜色

        pattern2 = xlwt.Pattern()
        pattern2.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern2.pattern_fore_colour = 5  # 背景颜色

        # 定义不同的excel style
        style1 = xlwt.XFStyle()
        style1.font = font
        style1.borders = borders
        style1.alignment = alignment
        style2 = xlwt.XFStyle()
        style2.borders = borders
        style2.alignment = alignment
        style2.pattern = pattern2
        style3 = xlwt.XFStyle()
        style3.borders = borders
        style3.font = font
        style3.pattern = pattern1
        style3.alignment = alignment
        style4 = xlwt.XFStyle()
        style4.borders = borders
        style4.font = font
        style4.pattern = pattern
        style4.alignment = alignment

        jsondata = request.POST
        if not jsondata:
            return to_json_data(errno=Code.UNKOWNERR, errmsg="出入数据不能为空")
        data = json.loads(jsondata['json'])
        c_list = data["content"].split('\n')
        No1 = 0
        down_list=[]
        for i in c_list:
            Q_obj = IdcP.objects.select_related('idcr','idcn','pup','tf','oip','off','supplier')\
                .only('idcr__rname','idcn__name','pup__pname','tf__tname','off__fname','oip__oname','supplier__sname')\
                .filter(is_delete=False,pn=i)
            down_list.append(Q_obj)
            if Q_obj:
                No1+=1
        if No1:
            # 创建工作薄
            ws = Workbook(encoding='utf-8')
            w = ws.add_sheet(u"sheet1",cell_overwrite_ok=True)
            w.row(0).height_mismatch = True
            w.row(0).height = 50 * 25
            # w.row(0).width_mismatch = True
            # w.col(0).width = 100 * 25
            # 合并单元格并设置样式
            # w.write_merge(0, 0, 0, len(bodytitle), tabletitle, style=style1)
            # w.write_merge(1, 1, 0, len(bodytitle), remark)

            # w.write(0, 0, "#SN",style=style3)
            # w.write(0, 1, "#内网网口",style=style3)
            # w.write(0, 2, "#内网网关，掩码，IP",style=style3)
            # w.write(0, 3, "#外网网口",style=style3)
            # w.write(0, 4, "#外网网关，掩码，IP",style=style3)
            # w.write(0, 5, "DHCP",style=style3)
            # w.write(0, 6, "安装模板",style=style3)
            a=0
            for n in ["#SN","#内网网口","#内网网关，掩码，IP","#外网网口","#外网网关，掩码，IP","DHCP","安装模板","工单核验"]:
                write_excel(w,n,a,style3)
                a += 1

            # 写入数据
            excel_row = 2
            for _ in down_list:
                for obj in _:
                    # w.col(0).width = len(obj.sn) * 500
                    # w.col(1).width = len("eth0,eth1,"+ obj.ntvlan) * 500
                    # w.col(2).width = len(obj.ngateway + obj.nmask + obj.nip) * 500
                    # w.col(3).width = 100 * 35
                    # w.col(4).width = 100 * 50
                    # w.col(5).width = len("dhcp") * 500
                    # w.col(6).width = len(obj.imode) * 500
                    nwvlan = "eth0,eth1,"+ obj.ntvlan
                    nwip = obj.ngateway + obj.nmask + obj.nip

                    if (obj.ntvlan != "") & len(obj.ntvlan) > 5:
                        wwvlan = "eth0,eth1,"+ obj.ntvlan.split(",")[1]
                    wwvlan = ""

                    if (obj.wgateway != "") & (obj.wmask != "") & (obj.wip != ""):
                        wwip = obj.wgateway + obj.wmask + obj.wip
                    wwip = ""

                    a=0
                    for n in [obj.sn,nwvlan, nwip,wwvlan,wwip,"dhcp",obj.imode,obj.remarks]:
                        len_col(w, a, n)
                        a += 1

                    w.write(excel_row, 0, obj.sn)
                    w.write(excel_row, 1, "eth0,eth1,"+ obj.ntvlan)
                    w.write(excel_row, 2, obj.ngateway + obj.nmask + obj.nip)
                    # w.write(excel_row, 3, obj.sn)
                    # w.write(excel_row, 4, obj.mt)
                    w.write(excel_row, 5, "dhcp")
                    w.write(excel_row, 6, obj.imode,style=style4)
                    w.write(excel_row, 7, obj.remarks,style=style2)
                    excel_row += 1
            # 检测文件是够存在
            # 方框中代码是保存本地文件使用，如不需要请删除该代码
            ###########################
            # exist_file = os.path.exists("test.xls")
            # if exist_file:
            #     os.remove(r"test.xls")
            # ws.save("test.xls")
            ############################
            sio = BytesIO()  # 这里原博客是StringIO.StringIO()，发现有点问题
            ws.save(sio)
            sio.seek(0)
            # final_filename = '建德内部资产.xls'
            # doc_filename = escape_uri_path(final_filename)
            response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=jdFAI.xls'
            response.write(sio.getvalue())
            return response
        else:
            return to_json_data(errno=Code.DBERR, errmsg="未查到相应数据")

    def len_byte(value):
        if value is None or value == "":
            return 10
        if type(value) != int:
            length = len(value)
            utf8_length = len(value.encode('utf-8'))
            length = (utf8_length - length) / 2 + length
        else:
            length = len(str(value))
        return int(length)


class SearchTest(LoginRequiredMixin,View):
    """
    search data from mysql
    """
    # def get(self,request):
    #     return render(request, 'searches/loves.html')

    def post(self,request):
        change_info(request)
        json_data=request.body
        if not json_data:
            return to_json_data(errno=Code.UNKOWNERR, errmsg="出入数据不能为空")
        data12 = json.loads(json_data.decode('utf8'))
        c_list = data12['content'].split('\n')

        data_list = []
        s_list = []
        num = 0
        for i in c_list:
            i = i.strip()
            property_query = IdcP.objects.select_related('idcr','idcn','supplier','tf','oip','pup','off').\
                only('id','pn','idcn__name','idcr__rname','supplier__sname','oip__oname','tf__tname','off__fname','pup__pname')\
                .filter(is_delete=False,pn=i)
            p = IdcP.objects.filter(is_delete=False)
            if property_query.first():
                num+=1
            if i not in s_list:
                for z in property_query:
                    data_list.append({
                        'number':num,
                        'id': z.id,
                        'idcn__name': z.idcn.name,
                        'idcr__rname': z.idcr.rname,
                        'pn': z.pn,
                        'sn': z.sn,
                        'mt': z.mt,
                        'cb': z.cb,
                        'ul': z.ul,
                        'nip': z.nip,
                        'nmask': z.nmask,
                        'ngateway': z.ngateway,
                        'wip': z.wip,
                        'wmask': z.wmask,
                        'wgateway': z.wgateway,
                        'mip': z.mip,
                        'mmask': z.mmask,
                        'mgateway': z.mgateway,
                        'ntvlan': z.ntvlan,
                        'navlan': z.navlan,
                        'imode': z.imode,
                        'env': z.env,
                        'transfer': z.tf.tname,
                        'pupdate': z.pup.pname,
                        'obip': z.oip.oname,
                        'equ_ty': z.equ_ty,
                        'arrtime': z.arrtime,
                        'move': z.moves,
                        'offline': z.off.fname,
                        'Hwc': z.Hwc,
                        're_install': z.re_install,
                        'remarks': z.remarks,
                        'supplier': z.supplier.sname,
                    })
                    s_list.append(i)

        data = {
            'pro_inf':data_list
        }

        return to_json_data(data=data)


    def put(self, request):
        change_info(request)
        """
        per property update
        :param request:
        :return:
        """
        json_data = request.body
        if not json_data:
            return to_json_data(errno=Code.UNKOWNERR,errmsg="出入数据不能为空")
        data = json.loads(json_data.decode('utf8'))

        pn = data['pn']
        idc_name = IdcN.objects.defer('create_time', 'update_time').filter(is_delete=False)
        idc_room = IdcR.objects.defer('create_time', 'update_time').filter(is_delete=False)
        idc_tf = TF.objects.defer('create_time', 'update_time').filter(is_delete=False)
        idc_pup = PuP.objects.defer('create_time', 'update_time').filter(is_delete=False)
        idc_obip = ObIp.objects.defer('create_time', 'update_time').filter(is_delete=False)
        idc_off = OffLine.objects.defer('create_time', 'update_time').filter(is_delete=False)
        idc_supplier = Supplier.objects.defer('create_time', 'update_time').filter(is_delete=False)

        p_exist = IdcP.objects.only('pn').filter(is_delete=False, pn=pn).first()
        if not p_exist:
            return to_json_data(errno=Code.UNKOWNERR,errmsg="无此资产编号")
        for _ in idc_name:
            if data['idc_name'] == _.name:
                p_exist.idcn_id = _.id
        for _ in idc_room:
            if data['idr_name'] == _.rname:
                p_exist.idcr_id = _.id
        for _ in idc_tf:
            if data['transfer'] == _.tname:
                p_exist.tf_id = _.id
        for _ in idc_pup:
            if data['pupdate'] == _.pname:
                p_exist.pup_id = _.id
        for _ in idc_obip:
            if data['obip'] == _.oname:
                p_exist.oip_id = _.id
        for _ in idc_off:
            if data['offline'] == _.fname:
                p_exist.off_id = _.id
        for _ in idc_supplier:
            if data['supplier'] == _.sname:
                p_exist.supplier_id = _.id
        p_exist.sn = data['sn']
        p_exist.mt = data['mt']
        p_exist.cb = data['cb']
        if data['ul']:
            data['ul']=int(data['ul'])
        p_exist.ul = data['ul']
        p_exist.nip = data['nip']
        p_exist.nmask = data['nmask']
        p_exist.ngateway = data['ngateway']
        p_exist.wip = data['wip']
        p_exist.wmask = data['wmask']
        p_exist.wgateway = data['wgateway']
        p_exist.mip = data['mip']
        p_exist.mmask = data['mmask']
        p_exist.mgateway = data['mgateway']
        p_exist.ntvlan = data['ntvlan']
        p_exist.navlan = data['navlan']
        p_exist.imode = data['imode']
        p_exist.env = data['env']
        p_exist.equ_ty = data['equ_ty']
        p_exist.arrtime = data['arrtime']
        if type(data['move']) == float:
            data['move'] = int(data['move'])
        p_exist.moves = data['move']
        p_exist.Hwc = data['Hwc']
        p_exist.re_install = data['re_install']
        p_exist.remarks = data['remarks']
        p_exist.save()
        return to_json_data()

#search for other information of property
class OtherSearch(LoginRequiredMixin,View):
    """
    other information of property
    """
    def  get(self,request):
        change_info(request)
        return render(request,'searches/others.html')

#search for other information of property
class TransferSearch(LoginRequiredMixin,View):
    """
    transfer information of property
    """
    def  get(self,request):
        change_info(request)
        try:
            idc_id = int(request.GET.get('idc_id', 0))
        except Exception as e:
            logger.info("idc_id标签错误：\n{}".format(e))
            idc_id = 0

        try:
            idcr_id = int(request.GET.get('idcr_id', 0))
        except Exception as e:
            logger.info("idcr_id标签错误：\n{}".format(e))
            idcr_id = 0

        try:
            tf_id = int(request.GET.get('tf_id', 0))
        except Exception as e:
            logger.info("tf_id标签错误：\n{}".format(e))
            tf_id = 0

        try:
            pup_id = int(request.GET.get('pup_id', 0))
        except Exception as e:
            logger.info("pup_id标签错误：\n{}".format(e))
            pup_id = 0

        try:
            obip_id = int(request.GET.get('obip_id', 0))
        except Exception as e:
            logger.info("obip_id标签错误：\n{}".format(e))
            obip_id = 0

        try:
            off_id = int(request.GET.get('off_id', 0))
        except Exception as e:
            logger.info("off_id标签错误：\n{}".format(e))
            off_id = 0

        try:
            supplier_id = int(request.GET.get('supplier_id', 0))
        except Exception as e:
            logger.info("supplier_id标签错误：\n{}".format(e))
            supplier_id = 0

        try:
            remarks_id = request.GET.get('remarks_id')
        except Exception as e:
            logger.info("remarks_id标签错误：\n{}".format(e))
            remarks_id = 0
        
        search_number = 0

        p_idc = IdcN.objects.only("id",'name').filter(is_delete=False)
        p_idcr = IdcR.objects.only("id",'rname').filter(is_delete=False,R_id=idc_id)
        p_tf = TF.objects.only('id', 'tname').filter(is_delete=False)
        p_pup = PuP.objects.only('id', 'pname').filter(is_delete=False)
        p_obip = ObIp.objects.only('id', 'oname').filter(is_delete=False)
        p_off = OffLine.objects.only('id', 'fname').filter(is_delete=False)
        p_supplier = Supplier.objects.only('id', 'sname').filter(is_delete=False)
        p_idcp = IdcP.objects.only('id', 'remarks').filter(is_delete=False)

        re_list =[]
        rn_list =[]
        rf_list =[]
        rm_list =[]
        num1 = 0


        for _ in p_idcp:
            if _.remarks:
                if _.remarks not in rn_list:
                    num1 += 1
                    remarks = str(num1)+"、"+_.remarks
                    remarks1 = _.remarks
                    rn_list.append(remarks1)
                    re_list.append(remarks)
        num1 = 0
        if not tf_id and not pup_id and not obip_id and not off_id and not supplier_id and not idc_id and not remarks_id:
            return render(request, 'searches/transfer.html', locals())
        property_query = IdcP.objects.only('pn', 'idcn__name','idcr__rname').\
            select_related('idcn', 'idcr').filter(is_delete=False)

        if idc_id:
            property_query = property_query.filter(idcn_id=idc_id)
            search_number = property_query.count()

        if idcr_id:
            property_query = property_query.filter(idcr_id=idcr_id)
            search_number = property_query.count()


        if tf_id:
            property_query = property_query.filter(tf_id=tf_id)
            search_number = property_query.count()


        if pup_id:
            property_query = property_query.filter(pup_id=pup_id)
            search_number = property_query.count()
            for _ in property_query:
                if _.remarks:
                    if _.remarks not in rm_list:
                        num1 += 1
                        remarks = str(num1) + "、" + _.remarks
                        remarks1 = _.remarks
                        rm_list.append(remarks1)
                        rf_list.append(remarks)

        if obip_id:
            property_query = property_query.filter(oip_id=obip_id)
            search_number = property_query.count()


        if off_id:
            property_query = property_query.filter(off_id=off_id)
            search_number = property_query.count()


        if supplier_id:
            property_query = property_query.filter(supplier_id=supplier_id)
            search_number = property_query.count()



        if remarks_id != '0':
            remarks_id = remarks_id.split("、")[1]
            property_query = property_query.filter(remarks=remarks_id)
            search_number = property_query.count()

        if rf_list:
            re_list = rf_list

        #获取第几页内容
        try:
            page = int(request.GET.get('page', 1))
        except Exception as e:
            logger.info("当前页数错误：\n{}".format(e))
            page = 1
        paginator = Paginator(property_query, constants.PER_PAGE_NEWS_COUNT)
        try:
            property_query = paginator.page(page)
        except EmptyPage:
            # 若用户访问的页数大于实际页数，则返回最后一页数据
            logging.info("用户访问的页数大于总页数。")
            property_query = paginator.page(paginator.num_pages)

        paginator_data = paginator_script.get_paginator_data(paginator, property_query)

        context = {
            'property_query': property_query,
            "idc_id":idc_id,
            "idcr_id": idcr_id,
            "tf_id": tf_id,
            "pup_id": pup_id,
            "obip_id": obip_id,
            "off_id": off_id,
            "supplier_id": supplier_id,
            "search_number": search_number,
            "p_idc":p_idc,
            "p_idcr": p_idcr,
            "p_tf": p_tf,
            "p_pup": p_pup,
            "p_obip": p_obip,
            "p_off": p_off,
            "p_supplier": p_supplier,
            "p_remarks": re_list,
            "other_param": urlencode({
                "idc_id": idc_id,
                "idcr_id": idcr_id,
                "tf_id": tf_id,
                "pup_id": pup_id,
                "obip_id": obip_id,
                "off_id": off_id,
                "supplier_id": supplier_id,
                "remarks_id":"1、"+ remarks_id,
                })
        }

        context.update(paginator_data)

        return render(request,'searches/transfer.html',context=context)

    def post(self,request):
        change_info(request)
        """
           导出excel表格
        """
        jsondata = request.POST
        if not jsondata:
            return to_json_data(errno=Code.UNKOWNERR, errmsg="传入数据不能为空")
        data = json.loads(jsondata['json'])
        if data['remarks_id'] != "0":
            remarks_id = data['remarks_id'].split("、")[1]
        else:
            remarks_id = data['remarks_id']
        idc_id = data["idc_id"]
        idcr_id = data["idcr_id"]
        transfer_id =data["transfer_id"]
        pupdate_id =data["pupdate_id"]
        obip_id =data["obip_id"]
        offline_id =data["offline_id"]
        supplier_id = data["supplier_id"]


        if (idc_id == '0' and transfer_id == '0' and pupdate_id == '0' and obip_id == '0'
                 and offline_id == '0' and supplier_id =='0' and idcr_id == '0' and remarks_id == '0'):
            return to_json_data(errno=Code.UNKOWNERR, errmsg="无下载数据")

        P_obj = IdcP.objects.select_related('idcr','idcn','pup','tf','oip','off','supplier')\
                .only('idcr__rname','idcn__name','pup__pname','tf__tname','off__fname','oip__oname','supplier__sname')

        if int(idc_id) > 0:
            P_obj = P_obj.filter(is_delete=False, idcn_id=idc_id)

        if int(idcr_id) > 0:
            P_obj = P_obj.filter(is_delete=False, idcr_id=idcr_id)

        if int(transfer_id) > 0 :
            P_obj = P_obj.filter(is_delete=False, tf_id=transfer_id)

        if int(pupdate_id) > 0:
            P_obj = P_obj.filter(is_delete=False, pup_id=pupdate_id)

        if int(obip_id) > 0:
            P_obj = P_obj.filter(is_delete=False, oip_id=obip_id)

        if int(offline_id) > 0:
            P_obj = P_obj.filter(is_delete=False, off_id=offline_id)

        if int(supplier_id) > 0:
            P_obj = P_obj.filter(is_delete=False, supplier_id=supplier_id)

        if remarks_id != '0':
            P_obj = P_obj.filter(is_delete=False, remarks=remarks_id)

        # 创建工作薄
        ws = Workbook(encoding='utf-8')
        w = ws.add_sheet(u"sheet1")
        w.write(0, 0, "机房名称")
        w.write(0, 1, "房间编号")
        w.write(0, 2, "资产编号")
        w.write(0, 3, "sn")
        w.write(0, 4, "机列号")
        w.write(0, 5, "机柜号")
        w.write(0, 6, "u位")
        w.write(0, 7, "内网ip")
        w.write(0, 8, "内网掩码")
        w.write(0, 9, "内网网关")
        w.write(0, 10, "外网ip")
        w.write(0, 11, "外网掩码")
        w.write(0, 12, "外网网关")
        w.write(0, 13, "带外ip")
        w.write(0, 14, "带外掩码")
        w.write(0, 15, "带外网关")
        w.write(0, 16, "内网trunk vlan")
        w.write(0, 17, "内网all vlan")
        w.write(0, 18, "安装模板")
        w.write(0, 19, "环境")
        w.write(0, 20, "设备型号")
        w.write(0, 21, "到货时间")
        w.write(0, 22, "是否交付")
        w.write(0, 23, "资产是否已更新")
        w.write(0, 24, "带外是否开通")
        w.write(0, 25, "搬迁")
        w.write(0, 26, "下线")
        w.write(0, 27, "硬件变更")
        w.write(0, 28, "重装")
        w.write(0, 29, "备注")
        w.write(0, 30, "供应商")
        # 写入数据
        excel_row = 1
        for obj in P_obj:
            w.write(excel_row, 0, obj.idcn.name)
            w.write(excel_row, 1, obj.idcr.rname)
            w.write(excel_row, 2, obj.pn)
            w.write(excel_row, 3, obj.sn)
            w.write(excel_row, 4, obj.mt)
            w.write(excel_row, 5, obj.cb)
            w.write(excel_row, 6, obj.ul)
            w.write(excel_row, 7, obj.nip)
            w.write(excel_row, 8, obj.nmask)
            w.write(excel_row, 9, obj.ngateway)
            w.write(excel_row, 10, obj.wip)
            w.write(excel_row, 11, obj.wmask)
            w.write(excel_row, 12, obj.wgateway)
            w.write(excel_row, 13, obj.mip)
            w.write(excel_row, 14, obj.mmask)
            w.write(excel_row, 15, obj.mgateway)
            w.write(excel_row, 16, obj.ntvlan)
            w.write(excel_row, 17, obj.navlan)
            w.write(excel_row, 18, obj.imode)
            w.write(excel_row, 19, obj.env)
            w.write(excel_row, 20, obj.equ_ty)
            w.write(excel_row, 21, obj.arrtime)
            w.write(excel_row, 22, obj.tf.tname)
            w.write(excel_row, 23, obj.pup.pname)
            w.write(excel_row, 24, obj.oip.oname)
            w.write(excel_row, 25, obj.moves)
            w.write(excel_row, 26, obj.off.fname)
            w.write(excel_row, 27, obj.Hwc)
            w.write(excel_row, 28, obj.re_install)
            w.write(excel_row, 29, obj.remarks)
            w.write(excel_row, 30, obj.supplier.sname)
            excel_row += 1
        # 检测文件是够存在
        # 方框中代码是保存本地文件使用，如不需要请删除该代码
        ###########################
        # exist_file = os.path.exists("test.xls")
        # if exist_file:
        #     os.remove(r"test.xls")
        # ws.save("test.xls")
        ############################
        sio = BytesIO()  # 这里原博客是StringIO.StringIO()，发现有点问题
        ws.save(sio)
        sio.seek(0)
        # final_filename = '建德内部资产.xls'
        # doc_filename = escape_uri_path(final_filename)
        response = HttpResponse(sio.getvalue(), content_type='application/vnd.ms-excel')
        response['Content-Disposition'] = 'attachment; filename=jd.xls'
        response.write(sio.getvalue())
        return response
